<?
//#####################################################################################

function get_country_text($iso2){
	$local_connect = $GLOBALS['master_db'];
	$query = "SELECT iso2,name FROM countries WHERE iso2='$iso2'";
	$local_connect->query($query);
	$res = $local_connect->getNext();
	return $res['name'];
}
//#####################################################################################

function list_sales_($wh='',$options='',$return_type='struct'){
	$map = array(
			'sale_id'=>'s.sale_id',
			'product_id'=>'s.product_id',
			'amount_total'=>'s.amount_total',
			'amount_wholesale'=>'s.amount_wholesale',
			'amount_referer'=>'s.amount_referer',
                        'amount_vat'=>'s.amount_vat',
			'amount_fee'=>'s.amount_fee',
			'sale_date'=>'to_char(s.date, \'YYYY-MM-DD HH24:MI:SS\')',
			'sale_number'=>'s.order_number',
			'period'=>'s.period',
			'sale_name'=>'s.name',
			'sale_type'=>'s.type',
			'sale_type_'=>'s.type',
			'payed_reseller'=>'s.payed_reseller',
			'payed_referer'=>'s.payed_referer',
			'product_type'=>'p.type',
			'product_name'=>'p.name ',
			'sale_period'=>'s.sale_period',
			'sale_state'=>'s.state',
			'purchase_id'=>'pr.purchase_id',
			'purchase_start_date'=>'pr.start_date',
			'purchase_expire_date'=>'pr.expire_date',
			'order_id'=>'o.order_id',
			'order_date'=>'to_char(o.date, \'YYYY-MM-DD HH24:MI:SS\')',
			'order_number'=>'o.order_number',
			'order_status'=>'o.order_status',
			'company_id'=>'o.company_id',
			'store_id'=>'o.store_id',
			'store'=>'st.title',
			'reseller_id'=>'o.reseller_id',
			'reseller'=>'r.name',
			'reseller_country'=>'r.country',
			'client_id'=>'o.client_id',
			'username'=>'cl.username',
			'client_server'=>'cl.server',
			'affiliate_id'=>'o.affiliate_id',
			'referer_id'=>'o.referer_id',
			'payment_method'=>'o.payment_method',
			'currency'=>'s.currency',
			'payment_currency'=>'o.payment_currency',
			'payment_currency_'=>'o.payment_currency',
			'payment_exchange'=>'o.payment_exchange',
			'client_company_name'=>'o.client_company_name',
			'client_vat_num'=>'o.client_vat_num',
			'client_firstname'=>'o.client_firstname',
			'client_lastname'=>'o.client_lastname',
			'client_email'=>'o.client_email',
			'client_address1'=>'o.client_address1',
			'client_address2'=>'o.client_address2',
			'client_city'=>'o.client_city',
			'client_state'=>'o.client_state',
			'client_zip'=>'o.client_zip',
			'client_country'=>'o.client_country',
			'client_country_'=>'o.client_country',
			'client_phone'=>'o.client_phone',
			'client_fax'=>'o.client_fax',
			'client_ip'=>'o.ip'		
	);
//	$table = "sales s LEFT JOIN products p ON(s.product_id=p.product_id) LEFT JOIN purchases pr ON(s.purchase_id=pr.purchase_id) LEFT JOIN orders o ON(s.order_id=o.order_id) LEFT JOIN stores st ON(o.store_id=st.store_id) LEFT JOIN resellers r ON (o.reseller_id=r.reseller_id) LEFT JOIN clients cl ON(o.client_id=cl.client_id)";
//	$table = "sales s LEFT JOIN orders o ON(s.order_id=o.order_id) LEFT JOIN clients cl ON(o.client_id=cl.client_id) LEFT JOIN resellers r ON (o.reseller_id=r.reseller_id) LEFT JOIN stores st ON(o.store_id=st.store_id) LEFT JOIN purchases pr ON(s.purchase_id=pr.purchase_id) LEFT JOIN registerdomain rgdm ON(pr.purchase_id=rgdm.purchase_id) LEFT JOIN products p ON(s.product_id=p.product_id)";

	$table = "sales s LEFT JOIN orders o ON(s.order_id=o.order_id) LEFT JOIN clients cl ON(o.client_id=cl.client_id) LEFT JOIN resellers r ON (o.reseller_id=r.reseller_id) LEFT JOIN stores st ON(o.store_id=st.store_id) LEFT JOIN purchases pr ON(s.purchase_id=pr.purchase_id) LEFT JOIN products p ON(s.product_id=p.product_id)";

	if($options[group_by] and is_array($options[group_by])){
		if(!in_array('payment_currency_',$options[group_by])){
			$map['count']="count(*)";
			$map[amount_total]="sum(s.amount_total)";
			$map[amount_wholesale]="sum(s.amount_wholesale)";
			$map[amount_reseller]="sum(s.amount_total-s.amount_wholesale)";
			$map[amount_referer]="sum(s.amount_referer)";
			$map[amount_vat]="sum(s.amount_vat)";
			$map[amount_fee]="sum(s.amount_fee)";
                }else{
			$map['count']="count(*)";
			$map[amount_total]="round(sum(s.amount_total*o.payment_exchange),2)";
			$map[amount_wholesale]="round(sum(s.amount_wholesale*o.payment_exchange),2)";
			$map[amount_reseller]="round(sum(s.amount_total-s.amount_wholesale),2)";
			$map[amount_referer]="round(sum(s.amount_referer),2)";
			$map[amount_vat]="round(sum(s.amount_vat*o.payment_exchange),2)";
			$map[amount_fee]="round(sum(s.amount_fee*o.payment_exchange),2)";                
                }
		foreach($options[group_by] as $v){
			if(in_array($v, array_keys($map))){
				$group_by[]=$map[$v];
				$params[]=$map[$v]." as ".$v;
			}
		}
		if($wh and is_array($wh)){
			foreach($wh as $wh_k => $wh_v){
				if(is_numeric($wh_v)){
					$where[] = $map[$wh_k]."=".$wh_v;
				}elseif(is_array($wh_v)){
					$tmp=array();
					foreach($wh_v as $vv){
						if(!in_array($wh_k,array('sale_date','order_date','sale_type_','client_country_'))){
							if(is_numeric($vv))
								$tmp[]=$map[$wh_k]."=".$vv;
							elseif($vv=='null')
								$tmp[]=$map[$wh_k]." is".$vv;
							elseif($vv=='isnull' or $vv=='notnull')
								$tmp[]=$map[$wh_k].' '.$vv;
							else
								$tmp[]=$map[$wh_k]."~*'$vv'";
						}else{
							if(in_array($wh_k,array('sale_type_','client_country_'))){
								$tmp[]=$map[$wh_k].substr($vv,0,2)."'".substr($vv,2,strlen($vv))."'";
							}else{
								$where[]=$map[$wh_k].substr($vv,0,2)."'".substr($vv,2,strlen($vv))."'";
							}
						}
					}
					if($tmp){
						$tmp = implode(' OR ',$tmp);
						$where[]="($tmp)";
					}
				}else{
					$where[] = $map[$wh_k]."~*'".$wh_v."'";
				}
			}
		}
		if($options[order_by] and ($options[order_by_]=='DESC' or $options[order_by_]=='ASC')){
			$order_by = "ORDER BY ".$map[($options[order_by])]." ".$options[order_by_];
		}
//		if($from_view){
//			
//		}
		if($where)
			$where = implode(' AND ',$where);
			
		if(!in_array('payment_currency_',$options[group_by])){
			$params[]="sum(s.amount_total) as amount_total";
			$params[]="sum(s.amount_wholesale) as amount_wholesale";
			$params[]="sum(s.amount_referer) as amount_referer";
			$params[]="sum(s.amount_total-s.amount_wholesale) as amount_reseller";
			$params[]="sum(s.amount_vat) as amount_vat";
			$params[]="sum(s.amount_fee) as amount_fee";
			$params[]="count(*)";               
                }else{
			$params[]="round(sum(s.amount_total*o.payment_exchange),2) as amount_total";
			$params[]="round(sum(s.amount_wholesale*o.payment_exchange),2) as amount_wholesale";
			$params[]="round(sum(s.amount_referer),2) as amount_referer";
			$params[]="round(sum(s.amount_total-s.amount_wholesale),2) as amount_reseller";
			$params[]="round(sum(s.amount_vat*o.payment_exchange),2) as amount_vat";
			$params[]="round(sum(s.amount_fee*o.payment_exchange),2) as amount_fee";
			$params[]="count(*)";               
                }
                
		$params = implode(',',$params);
		$group_by = "GROUP BY ".implode(',',$group_by);
		if($options[limit])
			$limit = "LIMIT ".$options[limit];
		else
			$limit = "LIMIT 50";
		if($options[offset])
			$offset = "OFFSET ".$options[offset];
		else
			$offset = "OFFSET 0";
	}else{
		foreach($map as $k => $v){
			$params[]=$v." as ".$k;
			if($wh and is_array($wh) and $wh[$k]){
				if(is_numeric($wh[$k])){
					$where[] = $v."=".$wh[$k];
				}elseif(is_array($wh[$k])){
					$tmp=array();
					foreach($wh[$k] as $vv){
						if(!in_array($k,array('sale_date','order_date','sale_type_','client_country_'))){
							if(is_numeric($vv))
								$tmp[] = $v."=".$vv;
							elseif($vv=='null')
								$tmp[] = $v." is".$vv;
							elseif($vv=='isnull' or $vv=='notnull')
								$tmp[] = $v.' '.$vv;
							else
								$tmp[] = "$v~*'$vv'";
						}else{
							if(in_array($k,array('sale_type_','client_country_'))){
								$tmp[] = $v.substr($vv,0,2)."'".substr($vv,2,strlen($vv))."'";
							}else{
								$where[]=$v.substr($vv,0,2)."'".substr($vv,2,strlen($vv))."'";
							}
						}
					}
					if($tmp){
						$tmp = implode(' OR ',$tmp);
						$where[]="($tmp)";
					}					
				}else{
					$where[] = $v."~*'".$wh[$k]."'";
				}
			}
		}
		if($options[order_by] and ($options[order_by_]=='DESC' or $options[order_by_]=='ASC')){
			$order_by = "ORDER BY ".$map[($options[order_by])]." ".$options[order_by_];
		}
		if($where)
			$where = implode(' AND ',$where);
		$params = implode(',',$params);
		if($options[limit])
			$limit = "LIMIT ".$options[limit];
		else
			$limit = "LIMIT 50";
		if($options[offset])
			$offset = "OFFSET ".$options[offset];
		else
			$offset = "OFFSET 0";
		if(!$order_by)
			$order_by = "ORDER BY s.date DESC";
	}

	//-------------------------------------------------
        $where = preg_replace("/o\.payment_method~\*/","o.payment_method=",$where);
        $where = preg_replace("/p\.type~\*/","p.type=",$where);
	//-------------------------------------------------

	$sql['type']= "SELECT";
	$sql['params']= $params;
	$sql['table']= $table;
	$sql['where']= $where;
	$sql['options']= $group_by." ".$order_by." ".$limit." ".$offset;
	$sql['return']= $return_type;
	if(! $result = sql_exec($sql,'','assoc')){
		return 0;
	}
	if($return_type == 'query'){
		return $result;
	}
	$sql['type']= "SELECT";
	$sql['params']= $params;
	$sql['table']= $table;
	$sql['where']= $where;
	$sql['options']= $group_by." ".$order_by;
	$sql['return']= "query";
	$rows = sql_num_rows(exec_query(sql_exec($sql,'','assoc')));
	return (array('result'=>$result,'num_rows'=>$rows));
}
//#####################################################################################

function generate_invoice_num($order_id,$sale_number=''){
	global $p1,$p2,$p3,$p4;
	if(!$invoices = list_sales_(array('order_id'=>$order_id),array('group_by'=>array('order_id','sale_date','sale_number','client_country','payment_method'),'order_by'=>'sale_date','order_by_'=>'ASC'))){
		return 0;
	}
	foreach($invoices['result'] as $k => $invoice){
		if($sale_number==$invoice['sale_number']){
			$index = $k;
		}
	}
	if(!$index){
		$ret['4']='x';
		$ret['0']=$p1['invoice'];
	}else{
		$ret['4']='x'.$index;
		$ret['0']=$p1['credit_note'];
	}
	$ret['1']=$p2[($invoices['result'][0]['payment_method'])];
	$sql['type']="SELECT";
	$sql['params']="client_vat_num";
	$sql['table']="orders";
	$sql['where']="order_id=$order_id";
	$sql['options']="";
	$sql['return']="hash";
	if(!$r=sql_exec($sql,'','assoc'))
		return 0;
	if($r['client_vat_num'])
		$vat = 'vat';
	else
		$vat = 'novat';
	foreach($p3 as $v){
		if(is_array($v) and in_array($invoices['result'][0]['client_country'],$v['in_country'])){
			$ret['2'] = $v[($vat)];
			break;
		}
	}
	if(!$ret['2'])
		$ret['2'] = $p3['else'];
	$ret['3'] = $p4+$order_id;
	ksort($ret);
	return (implode('-',$ret));
}
//#####################################################################################

function load_inv($invoice_num){
	global $p1,$p2,$p3,$p4;
	$inv_num_arr = split('-',$invoice_num);
	
	if(!$invoices = list_sales_(array('order_id'=>$inv_num_arr[3]-$p4),array('group_by'=>array('order_id','sale_date','client_country','payment_method'),'order_by'=>'sale_date','order_by_'=>'ASC'))){
		print_r($invoices);
		return 0;
	}

	for($i=1;$i<count($invoices[result]);$i++){
		if(preg_match("/$i/",$inv_num_arr[4]))
			$sd = $invoices[result][$i][sale_date];	
	}
	if(!$sd){
		$sd = $invoices[result][0][sale_date];
	}
	if(!$inv = list_sales_(array('order_id'=>$inv_num_arr[3]-$p4,'sale_date'=>$sd),array('group_by'=>array('order_id','reseller_id','reseller','payment_currency','payment_exchange','username','client_server','order_number','order_date','company_id','payment_method','currency','client_company_name','client_vat_num','client_firstname','client_lastname','client_email','client_address1','client_address2','client_city','client_state','client_zip','client_country','client_phone','client_fax','client_ip','product_id','sale_date','sale_number','period','sale_name','sale_type','sale_period','product_name','product_type','sale_number','store','store_id'),'order_by'=>'sale_date','order_by_'=>'ASC'))){
		return 0;
	}
	$sql['type'] = "SELECT";
	$sql['params'] = "";
	$sql['table'] = "company";
	$sql['where'] = "company_id=".$inv[result][0][company_id];
	$sql['options'] = "";
	$sql['return'] = "hash";
	if(!$company = sql_exec($sql,'','assoc'))
		return 0;
	return	(array('client'=>$inv[result],'company'=>$company));
}
//#####################################################################################

function gen_inv($order_id,$sale_number='',$type='a'){
	global $inv_p1,$inv_p2;
	$inv_n = preg_replace('/x/',$type,generate_invoice_num($order_id,$sale_number));
	$hash = load_inv($inv_n);
	return(generate_invoice($hash,$type));
}
//#####################################################################################

function generate_invoice($hash,$type='a'){
	global $p2,$inv_p1,$inv_p2;
        if(in_array($type,array('a','c'))){
                $type_ = 'a';
        }elseif(in_array($type,array('b','d','e'))){
                $type_ = 'c';
        }
	$SQL = "SELECT products from stores where store_id=".$hash['client'][0]['store_id'];
	$store_products = sql_fetch_assoc(exec_query($SQL));
	$store_products = unserialize(base64_decode($store_products['products']));
	foreach($hash[client] as $k=>$v){
			if(($hash['client'][$k]['sale_type']=='order' or $hash['client'][$k]['sale_type']=='renewal') and $hash['client'][$k]['product_type']=='domain' and !empty($store_products['domains_free'])){
				for($j=0;$j<count($hash[client]);$j++){
					if(($hash['client'][$j]['sale_type']=='order' or $hash['client'][$k]['sale_type']=='renewal') and $hash['client'][$j]['product_type']=='plan' and $store_products['domains_free'][($hash['client'][$j]['product_id'])]){
						$hash['client'][$j]['amount_total'] = $hash['client'][$j]['amount_total']+$hash['client'][$k]['amount_total'];
						$hash['client'][$k]['amount_total'] = sprintf('%.2f',0);
                                                $hash['client'][$j]['amount_wholesale'] = $hash['client'][$j]['amount_wholesale']+$hash['client'][$k]['amount_wholesale'];
                                                $hash['client'][$k]['amount_wholesale'] = sprintf('%.2f',0);
	                                        $hash['client'][$j]['amount_vat'] = $hash['client'][$j]['amount_vat']+$hash['client'][$k]['amount_vat'];
        	                                $hash['client'][$k]['amount_vat'] = sprintf('%.2f',0);
                	                        $hash['client'][$j]['amount_fee'] = $hash['client'][$j]['amount_fee']+$hash['client'][$k]['amount_fee'];
                        	                $hash['client'][$k]['amount_fee'] = sprintf('%.2f',0);
					}
				}
			}
	}
	foreach($hash[client] as $k=>$v){
		if(trim($v[payment_currency]) and $v[payment_currency]!='USD' and trim($v[payment_exchange])){
			$hash[client][$k][amount_total] = sprintf('%.2f',$hash[client][$k][amount_total]*$v[payment_exchange]);
			$hash[client][$k][amount_wholesale] = sprintf('%.2f',$hash[client][$k][amount_wholesale]*$v[payment_exchange]);
			$hash[client][$k][amount_vat] = sprintf('%.2f',$hash[client][$k][amount_vat]*$v[payment_exchange]);
			$hash[client][$k][amount_fee] = sprintf('%.2f',$hash[client][$k][amount_fee]*$v[payment_exchange]);
			$hash[client][$k][currency] = $v[payment_currency];
		}
	}
	unset($store_products);
	if($hash[client][0][amount_total]>=0){
		$ret[invoice_type]='Invoice';
		$invoice_type = 1;
	}else{
		$ret[invoice_type]='Credit Note';
		$invoice_type = 2;
	}
	
	$inv_n = preg_replace('/x/',$type_,generate_invoice_num($hash[client][0][order_id],$hash[client][0][sale_number]));	
	$ret[order_id] = $hash[client][0][order_id];
	$ret[store] = $hash[client][0][store];
	$ret[company_name] = $hash[company][name1];
	$ret[company_email] = $hash[company][email];
	$ret[company_phone] = $hash[company][phone];
	$ret[company_fax] = $hash[company][fax];
	$ret[company_country] = get_country_text($hash[company][country]);
	$ret[company_registration_id] = $hash[company][registration_id];
	$ret[company_vat] = $hash[company][vat];
	$ret[company_details] = $hash[company][name1]."\n".($hash[company][address2] ? $hash[company][address1].', '.$hash[company][address2] : $hash[company][address1])."\n".($hash[company][city].", ".$hash[company][state]." ".$hash[company][zip])."\n".get_country_text($hash[company][country])."\n";

	$ret[reseller]=$hash[client][0][reseller];
	$ret[client_server]=$hash[client][0][client_server];
	$ret[username]=$hash[client][0][username];
	$ret[client]=$hash[client][0][client_company_name] ? $hash[client][0][client_company_name] : $hash[client][0][client_firstname]." ".$hash[client][0][client_lastname];

	$ret[currency] = $hash[client][0][currency];
	$ret[invoice_number] = $inv_n;
	$ret[invoice_date] = date("F d, Y", mktime(substr($hash[client][0][sale_date],11,2), substr($hash[client][0][sale_date],14,2), substr($hash[client][0][sale_date],17,2), substr($hash[client][0][sale_date],5,2), substr($hash[client][0][sale_date],8,2), substr($hash[client][0][sale_date],0,4)));
	$ret[payment_method] = $inv_p1[($p2[($hash[client][0][payment_method])])];
	$ret[order_number] = $hash[client][0][sale_number];
	//$ret[order_date] = date("F d, Y; H:i:s", mktime(substr($hash[client][0][sale_date],11,2), substr($hash[client][0][sale_date],14,2), substr($hash[client][0][sale_date],17,2), substr($hash[client][0][sale_date],5,2), substr($hash[client][0][sale_date],8,2), substr($hash[client][0][sale_date],0,4))).' '.date("T");
  
  // sve: Patch GMT time. 
  include_once (LIBS_DIR.'SQL/sqltime.class.php');
  $ret[order_date] = date("F d, Y; H:i:s", SQLTime::GMT_Time($hash[client][0][sale_date], -2)).' GMT';

	if($type_ == 'a') {
		$ret[client_company_name] = $hash[client][0][client_company_name];
		$ret[client_full_name] = $hash[client][0][client_lastname] ? ucwords($hash[client][0][client_firstname]).' '.ucwords($hash[client][0][client_lastname]) : ucwords($hash[client][0][client_firstname]);
		$ret[client_phone] = $hash[client][0][client_phone];
		$ret[client_email] = $hash[client][0][client_email];
		$ret[client_vat_num] = $hash[client][0][client_vat_num];
		$ret[client_location] = ($hash[client][0][client_address2] ? $hash[client][0][client_address1].', '.$hash[client][0][client_address2] : $hash[client][0][client_address1])."\n"."\t".($hash[client][0][client_city].", ".$hash[client][0][client_state]." ".$hash[client][0][client_zip])."\n"."\t".get_country_text($hash[client][0][client_country])."\n";
		
    if($invoice_type ==1) {
			$invoice=1;
			foreach($hash[client] as $v){
				if($v[product_type]=='service'){
					$for_ = preg_replace('/X/',$v['count'],$inv_p2[($v[sale_name])]);
				}else
					$for_ = $v[sale_name];
				if($v[period]==0){
					$ret[payment_details].= "0 Month(s) @ ".$v[amount_total]." ".$v[currency]." = ".($v[amount_total])." ".$v[currency]." for ".$v[product_type]." ".$v[sale_type]." ".$for_."\n";
				}elseif($v[period]==1){
					$ret[payment_details].= $v[sale_period]." Month(s) @ ".sprintf('%.2f',($v[amount_total])/$v[sale_period])." ".$v[currency]." = ".($v[amount_total])." ".$v[currency]." for ".$v[product_type]." ".$v[sale_type]." ".$for_."\n";
				}elseif($v[period]==12){
					$ret[payment_details].= $v[period]." Month(s) @ ".sprintf('%.2f',($v[amount_total])/$v[period])." ".$v[currency]." = ".($v[amount_total])." ".$v[currency]." for ".$v[product_type]." ".$v[sale_type]." ".$for_."\n";				
				}
				$ret[amount]+=$v[amount_total];
				$ret[amount_vat]+=$v[amount_vat];
			}
			$ret[amount]=sprintf('%.2f',$ret[amount]);
			$ret[amount_vat]=sprintf('%.2f',$ret[amount_vat]);
			$ret[amount_total]=sprintf('%.2f',$ret[amount]+$ret[amount_vat]);
		}elseif($invoice_type ==2){
			$credit_note=1;
                        $ret[amount]=sprintf('%.2f',$hash[client][0][amount_total]);
                        $ret[amount_vat]=sprintf('%.2f',$hash[client][0][amount_vat]);
                        $ret[amount_total]=sprintf('%.2f',$ret[amount]+$ret[amount_vat]);
			if(!$tmp = list_sales_(array('order_id'=>$hash[client][0][order_id]),array('group_by'=>array('sale_date','order_id','payment_exchange','payment_currency'),'order_by'=>'sale_date','order_by_'=>'ASC')))
				return 0;
if(trim($tmp[result][0][payment_currency]) and $tmp[result][0][payment_currency]!='USD' and trim($tmp[result][0][payment_exchange])){
	$tmp[result][0][amount_total] = sprintf('%.2f',$tmp[result][0][amount_total]*$tmp[result][0][payment_exchange]);
	$tmp[result][0][amount_wholesale] = sprintf('%.2f',$tmp[result][0][amount_wholesale]*$tmp[result][0][payment_exchange]);
	$tmp[result][0][amount_vat] = sprintf('%.2f',$tmp[result][0][amount_vat]*$tmp[result][0][payment_exchange]);
	$tmp[result][0][amount_fee] = sprintf('%.2f',$tmp[result][0][amount_fee]*$tmp[result][0][payment_exchange]);
	$tmp[result][0][currency] = $tmp[result][0][payment_currency];
}
			$ret[order_date_] = date("F d, Y", mktime(substr($tmp[result][0][sale_date],11,2), substr($tmp[result][0][sale_date],14,2), substr($tmp[result][0][sale_date],17,2), substr($tmp[result][0][sale_date],5,2), substr($tmp[result][0][sale_date],8,2), substr($tmp[result][0][sale_date],0,4)));
			$ret[order_number_]=$hash[client][0][order_number];
			$ret[invoice_number_]= preg_replace('/x/',$type_,generate_invoice_num($tmp[result][0][order_id]));
			$ret[amount_]=sprintf('%.2f',$tmp[result][0][amount_total]);
			$ret[amount_vat_]=sprintf('%.2f',$tmp[result][0][amount_vat]);
			$ret[amount_total_]=sprintf('%.2f',$ret[amount_]+$ret[amount_vat_]);
			if($ret[amount_]==-$hash[client][0][amount_total]){
				$ret[refund_type]  = 'Full';
				$full = 1;
			}else{
				$ret[refund_type] = 'Partial';
				$partial = 1;
			}			
		}
		if($type=='a'){
			$ret[subject] = '';
		}elseif($type=='c'){
			$ret1['RESELLER'] = 1;
			$ret[subject]='Copy of ';
		}
		if($hash[company][vat])
			$company_vat=1;
		if($company_vat and $credit_note and $full)
			$ret1[credit_note_company_vat_full] = 1;
		if($company_vat and $credit_note and $partial)
			$ret1[credit_note_company_vat_partial] = 1;
		if(!$company_vat and $credit_note and $full)
			$ret1[credit_note_company_no_vat_full] = 1;
		if(!$company_vat and $credit_note and $partial)
			$ret1[credit_note_company_no_vat_partial] = 1;
		if($company_vat and $invoice)
			$ret1[invoice_company_vat] = 1;
		if(!$company_vat and $invoice)
			$ret1[invoice_company_no_vat] = 1;
	}elseif($type_ == 'c'){
		$sql[type]="SELECT";
		$sql[params]="company,first_name,last_name,email,phone,address1,address2,city,state,zip,country";
		$sql[table]="resellers";
		$sql[where]="reseller_id=".$hash[client][0][reseller_id];
		$sql[opitons]="";
		$sql['return']="hash";
		$reseller_details = sql_exec($sql,'','assoc');
		$ret[client_company_name] = $reseller_details[company];
		$ret[client_full_name] = $reseller_details[last_name] ? ucwords($reseller_details[first_name]).' '.ucwords($reseller_details[last_name]) : ucwords($reseller_details[first_name]);
		$ret[client_phone] = $reseller_details[phone];
		$ret[client_email] = $reseller_details[email];
		$ret[client_location] = ($reseller_details[address2] ? $reseller_details[address1].', '.$reseller_details[address2] : $reseller_details[address1])."\n"."\t".($reseller_details[city].", ".$reseller_details[state]." ".$reseller_details[zip])."\n"."\t".get_country_text($reseller_details[country])."\n";
		if($type=='d')
			$ret1[CUSTOMER]=1;
		else
			$ret1[RESELLERA]=1;
		$ret[invoice_number_]= preg_replace("/$type_/","a",$inv_n);
		if($invoice_type == 1){
			foreach($hash[client] as $v){
				if($v[product_type]=='service'){
					$for_ = preg_replace('/X/',$v['count'],$inv_p2[($v[sale_name])]);
				}else
					$for_ = $v[sale_name];
				if($v[period]==0){
					$ret[payment_details].= "0 Month(s) @ ".($v[amount_wholesale])." ".$v[currency]." = ".($v[amount_wholesale])." ".$v[currency]." for ".$v[product_type]." ".$v[sale_type]." ".$for_."\n";
				}elseif($v[period]==1){
					$ret[payment_details].= $v[sale_period]." Month(s) @ ".sprintf('%.2f',($v[amount_wholesale]/$v[sale_period]))." ".$v[currency]." = ".($v[amount_wholesale])." ".$v[currency]." for ".$v[product_type]." ".$v[sale_type]." ".$for_."\n";
				}elseif($v[period]==12){
					$ret[payment_details].= $v[period]." Month(s) @ ".sprintf('%.2f',($v[amount_wholesale]/$v[period]))." ".$v[currency]." = ".($v[amount_wholesale])." ".$v[currency]." for ".$v[product_type]." ".$v[sale_type]." ".$for_."\n";				
				}
				$ret[amount]+=$v[amount_wholesale];
//				$ret[amount_vat]+=$v[amount_vat];
				$ret[transaction_amount]+=$v[amount_total];
			}
			$ret[amount]=sprintf('%.2f',$ret[amount]);
//			$ret[amount_vat]=sprintf('%.2f',$ret[amount_vat]);
//			$ret[amount_total]=sprintf('%.2f',$ret[amount]+$ret[amount_vat]);
			$ret[amount_total]=$ret[amount];
			$ret[transaction_amount]=sprintf('%.2f',$ret[transaction_amount]);
			if($type=='b'){
				$ret1['WALLET_REFILL'] = 1;
			}elseif($type=='d'){
				$ret1['INVOICE'] = 1;
			}elseif($type=='e'){
				$ret1['MANUAL'] = 1;
			}
		}elseif($invoice_type ==2){
			if($type=='b'){
				$ret1['REFUND'] = 1;
                                if(!$tmp = list_sales_(array('order_id'=>$hash[client][0][order_id]),array('group_by'=>array('sale_date','order_id','payment_exchange','payment_currency'),'order_by'=>'sale_date','order_by_'=>'ASC')))
                                        return 0;
if(trim($tmp[result][0][payment_currency]) and $tmp[result][0][payment_currency]!='USD' and trim($tmp[result][0][payment_exchange])){
	$tmp[result][0][amount_total] = sprintf('%.2f',$tmp[result][0][amount_total]*$tmp[result][0][payment_exchange]);
	$tmp[result][0][amount_wholesale] = sprintf('%.2f',$tmp[result][0][amount_wholesale]*$tmp[result][0][payment_exchange]);
	$tmp[result][0][amount_vat] = sprintf('%.2f',$tmp[result][0][amount_vat]*$tmp[result][0][payment_exchange]);
	$tmp[result][0][amount_fee] = sprintf('%.2f',$tmp[result][0][amount_fee]*$tmp[result][0][payment_exchange]);
	$tmp[result][0][currency] = $tmp[result][0][payment_currency];
}
                                $ret[order_number_]=$hash[client][0][order_number];
                                $ret[invoice_number_]= preg_replace('/x/',$type_,generate_invoice_num($tmp[result][0][order_id]));
				$ret[invoice_date_] = date("F d, Y", mktime(substr($tmp[result][0][sale_date],11,2), substr($tmp[result][0][sale_date],14,2), substr($tmp[result][0][sale_date],17,2), substr($tmp[result][0][sale_date],5,2), substr($tmp[result][0][sale_date],8,2), substr($tmp[result][0][sale_date],0,4)));
                                $ret[amount]=sprintf('%.2f',$hash[client][0][amount_wholesale]);
                                $ret[amount_total] = sprintf('%.2f',$ret[amount]);
				$ret[amount_] = $tmp[result][0][amount_wholesale];
                                if($ret[amount]==-$tmp[result][0][amount_wholesale]){
                                        $ret[refund_type]  = 'Full';
                                }else{
                                        $ret[refund_type] = 'Partial';
                                }
			}elseif($type=='d'){
				$ret1['CREDIT_NOTE'] = 1;
				if(!$tmp = list_sales_(array('order_id'=>$hash[client][0][order_id]),array('group_by'=>array('sale_date','order_id','payment_currency','payment_exchange'),'order_by'=>'sale_date','order_by_'=>'ASC')))
					return 0;
if(trim($tmp[result][0][payment_currency]) and $tmp[result][0][payment_currency]!='USD' and trim($tmp[result][0][payment_exchange])){
	$tmp[result][0][amount_total] = sprintf('%.2f',$tmp[result][0][amount_total]*$tmp[result][0][payment_exchange]);
	$tmp[result][0][amount_wholesale] = sprintf('%.2f',$tmp[result][0][amount_wholesale]*$tmp[result][0][payment_exchange]);
	$tmp[result][0][amount_vat] = sprintf('%.2f',$tmp[result][0][amount_vat]*$tmp[result][0][payment_exchange]);
	$tmp[result][0][amount_fee] = sprintf('%.2f',$tmp[result][0][amount_fee]*$tmp[result][0][payment_exchange]);
	$tmp[result][0][currency] = $tmp[result][0][payment_currency];
}
                                $ret[order_number_]=$hash[client][0][order_number];
                                $ret[invoice_number_]= preg_replace("/$type_/","a",$inv_n);
                                $ret[amount]=sprintf('%.2f',$hash[client][0][amount_wholesale]);
                                $ret[amount_total]=sprintf('%.2f',$ret[amount]);
                        	if($ret[amount]==-$tmp[result][0][amount_wholesale]){
                                	$ret[refund_type]  = 'Full';
                        	}else{
                                	$ret[refund_type] = 'Partial';
                        	}
			}elseif($type=='e'){
				$ret1['REFUND'] = 1;
                                if(!$tmp = list_sales_(array('order_id'=>$hash[client][0][order_id]),array('group_by'=>array('sale_date','order_id','payment_exchange','payment_currency'),'order_by'=>'sale_date','order_by_'=>'ASC')))
                                        return 0;
if(trim($tmp[result][0][payment_currency]) and $tmp[result][0][payment_currency]!='USD' and trim($tmp[result][0][payment_exchange])){
	$tmp[result][0][amount_total] = sprintf('%.2f',$tmp[result][0][amount_total]*$tmp[result][0][payment_exchange]);
	$tmp[result][0][amount_wholesale] = sprintf('%.2f',$tmp[result][0][amount_wholesale]*$tmp[result][0][payment_exchange]);
	$tmp[result][0][amount_vat] = sprintf('%.2f',$tmp[result][0][amount_vat]*$tmp[result][0][payment_exchange]);
	$tmp[result][0][amount_fee] = sprintf('%.2f',$tmp[result][0][amount_fee]*$tmp[result][0][payment_exchange]);
	$tmp[result][0][currency] = $tmp[result][0][payment_currency];
}
                                $ret[order_number_]=$hash[client][0][order_number];
                                $ret[invoice_number_]= preg_replace('/x/',$type_,generate_invoice_num($tmp[result][0][order_id]));
                                $ret[invoice_date_] = date("F d, Y", mktime(substr($tmp[result][0][sale_date],11,2), substr($tmp[result][0][sale_date],14,2), substr($tmp[result][0][sale_date],17,2), substr($tmp[result][0][sale_date],5,2), substr($tmp[result][0][sale_date],8,2), substr($tmp[result][0][sale_date],0,4)));
                                $ret[amount]=sprintf('%.2f',$hash[client][0][amount_wholesale]);
                                $ret[amount_total] = sprintf('%.2f',$ret[amount]);
                                $ret[amount_] = $tmp[result][0][amount_wholesale];
                                if($ret[amount]==-$tmp[result][0][amount_wholesale]){
                                        $ret[refund_type]  = 'Full';
                                }else{
                                        $ret[refund_type] = 'Partial';
                                }
			}
		}
		
	}	
	foreach($ret as $k => $v){
		if($v and in_array($k,array('client_phone','client_email','client_company_name','client_vat_num','company_phone','company_fax','amount_vat')))
			if($k!='amount_vat')
				$ret1[$k]=1;
			elseif($v > 0)
				$ret1[$k]=1;
	}
	foreach($ret1 as $k=>$v) $ret_1[strtoupper($k)] = $v;
	for($i=1;$i<=5;$i++)
		$ret_1['BODY'.$i] = 1;		
	return (array('variables'=>$ret,'blocks'=>$ret_1));
}

function get_self_billing_invoice_details($payment_id){
	$local_db_connection = $GLOBALS['master_db'];
	$query = "
		SELECT 
			p.payment_id,
			p.payout_account_id,
			p.date,
			p.reseller_id,
			p.first_name as p_first_name,
			p.last_name as p_last_name,
			p.email as p_email,
			p.phone as p_phone,
			p.address1 as p_address1,
			p.address2 as p_address2,
			p.city as p_city,
			p.state as p_state,
			p.zip as p_zip,
			p.country as p_country,
			p.swift_code,
			p.bank_name,
			p.bank_address,
			p.account_number,
			p.routing_number,
			p.account_holder,
			p.payment_method,
			p.tax_id,
			p.status,
			p.amount,
			p.check_number,
			p.paypal_email,
			p.memo,
			p.currency,
			p.paypal_transaction_id,
			p.change_status_date,
			r.name as reseller,
			r.company as reseller_company_name,
			r.first_name as r_first_name,
			r.last_name as r_last_name,
			r.email as r_email,
			r.phone as r_phone,
			r.address1 as r_address1,
			r.address2 as r_address2,
			r.city as r_city,
			r.state as r_state,
			r.zip as r_zip,
			r.country as r_country
		FROM payments p LEFT JOIN resellers r USING(reseller_id)
		WHERE payment_id=($payment_id)
	";

	$local_db_connection->query($query);
	if($local_db_connection->error){
		echo 'Error : '.$local_db_connection->error.'<br>'.$query;
		return false;
	}

	$tmp_res = $local_db_connection->getNext();
	
	$res['variables']['reseller'] = $tmp_res['reseller'];
	$res['variables']['reseller_company_name'] = trim($tmp_res['reseller_company_name']);
	$res['variables']['payment_id'] = $tmp_res['payment_id'];
	$res['variables']['memo'] = $tmp_res['memo'];
	$res['variables']['amount'] = $tmp_res['amount'];
	$res['variables']['payment_date'] = $tmp_res['change_status_date'];
	$res['variables']['payment_method'] = $tmp_res['payment_method'];

	if(trim($tmp_res['p_first_name']) or trim($tmp_res['p_last_name'])){
		if(trim($tmp_res['p_first_name']) != trim($tmp_res['p_last_name'])){
			$res['variables']['reseller_full_name'] = ucwords(trim($tmp_res['p_first_name']).' '.trim($tmp_res['p_last_name']));
		}else{
			$res['variables']['reseller_full_name'] = ucwords(trim($tmp_res['p_first_name']));
		}
	}else{
		if(trim($tmr_res['r_first_name']) != trim($tmr_res['r_last_name'])){
			$res['variables']['reseller_full_name'] = ucwords(trim($tmr_res['r_first_name']).' '.trim($tmr_res['r_last_name']));
		}else{
			$res['variables']['reseller_full_name'] = ucwords(trim($tmr_res['r_first_name']));
		}	
	}
	
	if($tmp_res['p_address1'] and $tmp_res['p_city'] and $tmp_res['p_state'] and $tmp_res['p_zip'] and $tmp_res['p_country']){
		$res['variables']['reseller_location'] = 
			($tmp_res['p_address2'] ? ucwords($tmp_res['p_address1'].', '.$tmp_res['p_address2']) : ucwords($tmp_res['p_address1']))."\n".
			(ucwords($tmp_res['p_city']).', '.strtoupper($tmp_res['p_state'].' '.$tmp_res['p_zip']))."\n".
			(get_country_text(trim($tmp_res['p_country'])));
	}else{
		$res['variables']['reseller_location'] = 
			($tmr_res['r_address2'] ? ucwords($tmr_res['r_address1'].', '.$tmr_res['r_address2']) : ucwords($tmr_res['r_address1']))."\n".
			(ucwords($tmr_res['r_city']).', '.strtoupper($tmr_res['r_state'].' '.$tmr_res['r_zip']))."\n".
			(get_country_text(trim($tmr_res['r_country'])));
	}

	if($tmp_res['p_email']){
		$res['variables']['reseller_email'] = $tmp_res['p_email'];
	}else{
		$res['variables']['reseller_email'] = $tmp_res['r_email'];
	}

        if($res['variables']['payment_method']=='check'){
                $res['variables']['payment_details'] = 'Check Number : '.$tmp_res['check_number'];
        }elseif($res['variables']['payment_method']=='paypal'){
                $res['variables']['payment_details'] = 'PayPal Email : '.$tmp_res['paypal_email'].', Transaction ID : '.$tmp_res['paypal_transaction_id'];
        }elseif($res['variables']['payment_method']=='wire'){
                $res['variables']['payment_details'] = "
        Swift Code : $tmp_res[swift_code]
        Bank Name : $tmp_res[bank_name]
        Bank Address : $tmp_res[bank_address]
        Account Number : $tmp_res[account_number]
        Routing Number : $tmp_res[routing_number]
        Account Holder : $tmp_res[account_holder]
                ";
        }elseif($res['variables']['payment_method']=='wallet'){
                $res['variables']['payment_details'] = 'Wallet Commission Payment';
        }

        if($res['variables']['reseller_company_name']){
                $res['blocks'][] = 'reseller_company_name';
        }

        return $res;

}
?>
